package com.mangguo.tingjian.user.mapper;

import com.mangguo.tingjian.user.controller.req.UserImageReq;
import com.mangguo.tingjian.user.dto.*;
import com.mangguo.tingjian.user.entity.DwUserActiveMini;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 激活用户-小程序 Mapper 接口
 * </p>
 *
 * @author auto
 * @since 2019-11-25
 */
@Mapper
public interface DwUserActiveMiniMapper extends BaseMapper<DwUserActiveMini> {
    @Select("select count(DISTINCT imei) as count from dw_user_active_mini")
    Long countAllUniqueImei();

    @Select("select count(DISTINCT imei) as count from dw_user_active_mini where mdate >= #{start} and mdate <= #{end}")
    Long countUniqueImeiBetweenDate(@Param("start")Integer start,
                                    @Param("end") Integer end);

    @Select("select count(DISTINCT imei) as count,mYear,mMonth from dw_user_active_mini group by mYear,mMonth")
    List<CountResult> countUniqueImeiByMonth();

    @Select("select count(DISTINCT imei) as count,mDate from dw_user_active_mini where mdate >= #{start} and mdate <= #{end} group by mDate")
    List<CountResult> countUniqueImeiByDate(@Param("start")Integer start,
                                            @Param("end") Integer end);

//    @Select("select count(1) from (select imei from dw_user_active_mini group by imei) as a")
//    Long countAllUniqueImei();
//
//    @Select("select count(1) from (select imei from dw_user_active_mini where mdate >= #{start} and mdate <= #{end} group by imei) as a")
//    Long countUniqueImeiBetweenDate(@Param("start")Integer start,
//                                    @Param("end") Integer end);
//
//    @Select("select count(1) as count, mYear,mMonth from (select imei,mYear,mMonth from dw_user_active_mini group by imei,mYear,mMonth) as a group by mYear,mMonth")
//    List<CountResult> countUniqueImeiByMonth();
//
//    @Select("select count(1) as count, mDate from (select imei,mDate from dw_user_active_mini where mdate >= #{start} and mdate <= #{end} group by imei,mDate) as a group by mDate")
//    List<CountResult> countUniqueImeiByDate(@Param("start")Integer start,
//                                            @Param("end") Integer end);

    //    @Select("select count(1) as count, mDate from (select imei,mDate from dw_user_active_mini where mdate >= #{start} and mdate <= #{end} and osid in (${osid}) and (city in (${city}) or province in (${province})) group by imei,mDate) as a group by mDate")
    @Select("<script>"
            + "SELECT count(1) AS count, mDate "
            + "FROM (SELECT imei,mDate from dw_user_active_mini "
            + "<where> "
            + "<if test='userImageReq.getStart() != null'> "
            + "mdate &gt;= #{userImageReq.start}  AND "
            + "</if>"
            + "<if test='userImageReq.getEnd() != null'> "
            + "mdate &lt;= #{userImageReq.end}  AND "
            + "</if>"
            + "<if test='userImageReq.getOsid() != null and userImageReq.getOsid().size()>0'> "
            + "osid IN "
            + "<foreach item='osid' collection='userImageReq.osid' open='(' close=')' separator=','> "
            + "#{osid}"
            + "</foreach> AND "
            + "</if>"

            + "<if test='userImageReq.getChannel() != null and userImageReq.getChannel().size()>0'> "
            + "(channelId IN "
            + "<foreach item='channel' collection='userImageReq.channel' open='(' close=')' separator=','> "
            + "#{channel}"
            + "</foreach> OR subChannelId IN"
            + "<foreach item='channel' collection='userImageReq.channel' open='(' close=')' separator=','> "
            + "#{channel}"
            +  "</foreach> )AND "
            + "</if>"


            + "<if test='(userImageReq.getCity() != null and userImageReq.getCity().size()>0) and (userImageReq.getProvince() != null and userImageReq.getProvince().size()>0) and (userImageReq.getCity().get(0) != -1 and userImageReq.getProvince().get(0) != -1) '> "
            + "(city IN "
            + "<foreach item='city' collection='userImageReq.city' open='(' close=')' separator=','> "
            + "#{city}"
            + "</foreach> OR province IN"
            + "<foreach item='province' collection='userImageReq.province' open='(' close=')' separator=','> "
            + "#{province}"
            +  "</foreach> )AND "
            + "</if>"
            + "mDate is not null"
            + "</where>"

            + "group by imei,mDate) as a group by mDate order by mDate"
            + "</script>")
    List<CountResult> countUniqueImeiByDateBetweenDate(@Param("userImageReq") UserImageReq userImageReq);

    //    @Select("select count(1) as count, channelId from (select imei,channelId from dw_user_active_mini where mdate >= #{start} and mdate <= #{end} and osid in (${osid}) and (city in (${city}) or province in (${province}))  and channelId is not null  group by imei,channelId)as a group by channelId order by count desc limit 5")
    @Select("<script>"
            + "SELECT count(1) AS count, channelId "
            + "FROM (SELECT imei,channelId from dw_user_active_mini "
            + "<where> "
            + "<if test='userImageReq.getStart() != null'> "
            + "mdate &gt;= #{userImageReq.start}  AND "
            + "</if>"
            + "<if test='userImageReq.getEnd() != null'> "
            + "mdate &lt;= #{userImageReq.end}  AND "
            + "</if>"
            + "<if test='userImageReq.getOsid() != null and userImageReq.getOsid().size()>0'> "
            + "osid IN "
            + "<foreach item='osid' collection='userImageReq.osid' open='(' close=')' separator=','> "
            + "#{osid}"
            + "</foreach> AND "
            + "</if>"

            + "<if test='userImageReq.getChannel() != null and userImageReq.getChannel().size()>0'> "
            + "(channelId IN "
            + "<foreach item='channel' collection='userImageReq.channel' open='(' close=')' separator=','> "
            + "#{channel}"
            +  "</foreach> )AND "
            + "</if>"

            + "<if test='(userImageReq.getCity() != null and userImageReq.getCity().size()>0) and (userImageReq.getProvince() != null and userImageReq.getProvince().size()>0) and (userImageReq.getCity().get(0) != -1 and userImageReq.getProvince().get(0) != -1) '> "
            + "(city IN "
            + "<foreach item='city' collection='userImageReq.city' open='(' close=')' separator=','> "
            + "#{city}"
            + "</foreach> OR province IN"
            + "<foreach item='province' collection='userImageReq.province' open='(' close=')' separator=','> "
            + "#{province}"
            +  "</foreach> )AND "
            + "</if>"
            + "channelId is not null"
            + "</where>"

            + "group by imei,channelId) as a group by channelId order by count desc limit 5"
            + "</script>")
    List<ChannelResult> countUniqueImeiByChannelIdBetweenDate(@Param("userImageReq") UserImageReq userImageReq);

    @Select("<script>"
            + "SELECT count(1) AS count, subchannelId "
            + "FROM (SELECT imei,subchannelId from dw_user_active_mini "
            + "<where> "
            + "<if test='userImageReq.getStart() != null'> "
            + "mdate &gt;= #{userImageReq.start}  AND "
            + "</if>"
            + "<if test='userImageReq.getEnd() != null'> "
            + "mdate &lt;= #{userImageReq.end}  AND "
            + "</if>"
            + "<if test='userImageReq.getOsid() != null and userImageReq.getOsid().size()>0'> "
            + "osid IN "
            + "<foreach item='osid' collection='userImageReq.osid' open='(' close=')' separator=','> "
            + "#{osid}"
            + "</foreach> AND "
            + "</if>"

            + "<if test='userImageReq.getChannel() != null and userImageReq.getChannel().size()>0'> "
            + "(subchannelId IN "
            + "<foreach item='channel' collection='userImageReq.channel' open='(' close=')' separator=','> "
            + "#{channel}"
            +  "</foreach> )AND "
            + "</if>"

            + "<if test='(userImageReq.getCity() != null and userImageReq.getCity().size()>0) and (userImageReq.getProvince() != null and userImageReq.getProvince().size()>0) and (userImageReq.getCity().get(0) != -1 and userImageReq.getProvince().get(0) != -1) '> "
            + "(city IN "
            + "<foreach item='city' collection='userImageReq.city' open='(' close=')' separator=','> "
            + "#{city}"
            + "</foreach> OR province IN"
            + "<foreach item='province' collection='userImageReq.province' open='(' close=')' separator=','> "
            + "#{province}"
            +  "</foreach> )AND "
            + "</if>"
            + "subchannelId is not null"
            + "</where>"

            + "group by imei,subchannelId) as a group by subchannelId order by count desc limit 5"
            + "</script>")
    List<ChannelResult> countUniqueImeiBySubchannelIdBetweenDate(@Param("userImageReq") UserImageReq userImageReq);

    //    @Select("select count(1) as count, city from (select imei,city from dw_user_active_mini where mdate >= #{start} and mdate <= #{end} and osid in (${osid}) and (city in (${city}) or province in (${province})) group by imei,city) and city != null as a group by city order by count desc limit 5")
    @Select("<script>"
            + "SELECT count(1) AS count, city "
            + "FROM (SELECT imei,city from dw_user_active_mini "
            + "<where> "
            + "<if test='userImageReq.getStart() != null'> "
            + "mdate &gt;= #{userImageReq.start}  AND "
            + "</if>"
            + "<if test='userImageReq.getEnd() != null'> "
            + "mdate &lt;= #{userImageReq.end}  AND "
            + "</if>"
            + "<if test='userImageReq.getOsid() != null and userImageReq.getOsid().size()>0'> "
            + "osid IN "
            + "<foreach item='osid' collection='userImageReq.osid' open='(' close=')' separator=','> "
            + "#{osid}"
            + "</foreach> AND "
            + "</if>"

            + "<if test='userImageReq.getChannel() != null and userImageReq.getChannel().size()>0'> "
            + "(channelId IN "
            + "<foreach item='channel' collection='userImageReq.channel' open='(' close=')' separator=','> "
            + "#{channel}"
            + "</foreach> OR subChannelId IN"
            + "<foreach item='channel' collection='userImageReq.channel' open='(' close=')' separator=','> "
            + "#{channel}"
            +  "</foreach> )AND "
            + "</if>"

            + "<if test='(userImageReq.getCity() != null and userImageReq.getCity().size()>0) and (userImageReq.getProvince() != null and userImageReq.getProvince().size()>0) and (userImageReq.getCity().get(0) != -1 and userImageReq.getProvince().get(0) != -1) '> "
            + "(city IN "
            + "<foreach item='city' collection='userImageReq.city' open='(' close=')' separator=','> "
            + "#{city}"
            + "</foreach> OR province IN"
            + "<foreach item='province' collection='userImageReq.province' open='(' close=')' separator=','> "
            + "#{province}"
            +  "</foreach> )AND "
            + "</if>"
            + "city is not null"
            + "</where>"

            + "group by imei,city) as a group by city order by count desc limit 5"
            + "</script>")
    List<CityResult> countUniqueImeiByCityBetweenDate(@Param("userImageReq") UserImageReq userImageReq);

    //    @Select("select count(1) as count, province from (select imei,province from dw_user_active_mini where mdate >= #{start} and mdate <= #{end} and osid in (${osid}) and (city in (${city}) or province in (${province})) and province != null group by imei,province) as a group by province order by count desc limit 5")
    @Select("<script>"
            + "SELECT count(1) AS count, province "
            + "FROM (SELECT imei,province from dw_user_active_mini "
            + "<where> "
            + "<if test='userImageReq.getStart() != null'> "
            + "mdate &gt;= #{userImageReq.start}  AND "
            + "</if>"
            + "<if test='userImageReq.getEnd() != null'> "
            + "mdate &lt;= #{userImageReq.end}  AND "
            + "</if>"
            + "<if test='userImageReq.getOsid() != null and userImageReq.getOsid().size()>0'> "
            + "osid IN "
            + "<foreach item='osid' collection='userImageReq.osid' open='(' close=')' separator=','> "
            + "#{osid}"
            + "</foreach> AND "
            + "</if>"

            + "<if test='userImageReq.getChannel() != null and userImageReq.getChannel().size()>0'> "
            + "(channelId IN "
            + "<foreach item='channel' collection='userImageReq.channel' open='(' close=')' separator=','> "
            + "#{channel}"
            + "</foreach> OR subChannelId IN"
            + "<foreach item='channel' collection='userImageReq.channel' open='(' close=')' separator=','> "
            + "#{channel}"
            +  "</foreach> )AND "
            + "</if>"


            + "<if test='(userImageReq.getCity() != null and userImageReq.getCity().size()>0) and (userImageReq.getProvince() != null and userImageReq.getProvince().size()>0) and (userImageReq.getCity().get(0) != -1 and userImageReq.getProvince().get(0) != -1) '> "
            + "(city IN "
            + "<foreach item='city' collection='userImageReq.city' open='(' close=')' separator=','> "
            + "#{city}"
            + "</foreach> OR province IN"
            + "<foreach item='province' collection='userImageReq.province' open='(' close=')' separator=','> "
            + "#{province}"
            +  "</foreach> )AND "
            + "</if>"
            + "province is not null"
            + "</where>"

            + "group by imei,province) as a group by province order by count desc limit 5"
            + "</script>")
    List<ProvinceResult> countUniqueImeiByProvinceBetweenDate(@Param("userImageReq") UserImageReq userImageReq);



    @Select("<script>"
            + "SELECT count(1) AS count, platform "
            + "FROM (SELECT imei,platform from dw_user_active_mini "
            + "<where> "
            + "<if test='userImageReq.getStart() != null'> "
            + "mdate &gt;= #{userImageReq.start}  AND "
            + "</if>"
            + "<if test='userImageReq.getEnd() != null'> "
            + "mdate &lt;= #{userImageReq.end}  AND "
            + "</if>"
            + "<if test='userImageReq.getOsid() != null and userImageReq.getOsid().size()>0'> "
            + "osid IN "
            + "<foreach item='osid' collection='userImageReq.osid' open='(' close=')' separator=','> "
            + "#{osid}"
            + "</foreach> AND "
            + "</if>"

            + "<if test='userImageReq.getChannel() != null and userImageReq.getChannel().size()>0'> "
            + "(channelId IN "
            + "<foreach item='channel' collection='userImageReq.channel' open='(' close=')' separator=','> "
            + "#{channel}"
            + "</foreach> OR subChannelId IN"
            + "<foreach item='channel' collection='userImageReq.channel' open='(' close=')' separator=','> "
            + "#{channel}"
            +  "</foreach> )AND "
            + "</if>"


            + "<if test='(userImageReq.getCity() != null and userImageReq.getCity().size()>0) and (userImageReq.getProvince() != null and userImageReq.getProvince().size()>0) and (userImageReq.getCity().get(0) != -1 and userImageReq.getProvince().get(0) != -1) '> "
            + "(city IN "
            + "<foreach item='city' collection='userImageReq.city' open='(' close=')' separator=','> "
            + "#{city}"
            + "</foreach> OR province IN"
            + "<foreach item='province' collection='userImageReq.province' open='(' close=')' separator=','> "
            + "#{province}"
            +  "</foreach> )AND "
            + "</if>"
            + "platform is not null"
            + "</where>"

            + "group by imei,platform) as a group by platform order by count desc"
            + "</script>")
    List<PlatformResult> countUniqueImeiByPlatformBetweenDate(@Param("userImageReq") UserImageReq userImageReq);

    @Select("<script>"
            + "SELECT count(1) AS count, osid, system "
            + "FROM (SELECT imei, osid, system from dw_user_active_mini "
            + "<where> "
            + "<if test='userImageReq.getStart() != null'> "
            + "mdate &gt;= #{userImageReq.start}  AND "
            + "</if>"
            + "<if test='userImageReq.getEnd() != null'> "
            + "mdate &lt;= #{userImageReq.end}  AND "
            + "</if>"
            + "<if test='userImageReq.getOsid() != null and userImageReq.getOsid().size()>0'> "
            + "osid IN "
            + "<foreach item='osid' collection='userImageReq.osid' open='(' close=')' separator=','> "
            + "#{osid}"
            + "</foreach> AND "
            + "</if>"

            + "<if test='userImageReq.getChannel() != null and userImageReq.getChannel().size()>0'> "
            + "(channelId IN "
            + "<foreach item='channel' collection='userImageReq.channel' open='(' close=')' separator=','> "
            + "#{channel}"
            + "</foreach> OR subChannelId IN"
            + "<foreach item='channel' collection='userImageReq.channel' open='(' close=')' separator=','> "
            + "#{channel}"
            +  "</foreach> )AND "
            + "</if>"


            + "<if test='(userImageReq.getCity() != null and userImageReq.getCity().size()>0) and (userImageReq.getProvince() != null and userImageReq.getProvince().size()>0) and (userImageReq.getCity().get(0) != -1 and userImageReq.getProvince().get(0) != -1) '> "
            + "(city IN "
            + "<foreach item='city' collection='userImageReq.city' open='(' close=')' separator=','> "
            + "#{city}"
            + "</foreach> OR province IN"
            + "<foreach item='province' collection='userImageReq.province' open='(' close=')' separator=','> "
            + "#{province}"
            +  "</foreach> )AND "
            + "</if>"
            + "system is not null"
            + "</where>"

            + "group by imei, osid, system) as a group by osid, system order by count desc"
            + "</script>")
    List<SystemResult> countUniqueImeiBySystemBetweenDate(@Param("userImageReq") UserImageReq userImageReq);
}
